package com.employees.dao.impl;

import java.sql.SQLException;

import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import com.employees.bean.Employee;
import com.employees.bean.Employeeinfo;
import com.employees.bean.Pager;
import com.employees.dao.EmployeeDao;

public class EmployeeDaoImpl extends HibernateDaoSupport implements EmployeeDao {

	public List findAllEmployee() {
		// TODO Auto-generated method stub
		try {
			String hql = "from Employee  employee,Employeeinfo  employeeinfo,Employeeskill employeeskill where employee.no=employeeinfo.employee.no and employee.no=employeeskill.employee.no";

			Query query = this.getSession().createQuery(hql);
			List list = query.list();
			return list;
		} catch (HibernateException e) {
			e.printStackTrace();
			return null;
		}

	}

	public Employee getEmployee(String no, String name) {
		String hql = "from Employee where No='" + no + "'and name='" + name
				+ "'";

		try {
			Query query = this.getSession().createQuery(hql);
			Employee user = (Employee) query.uniqueResult();
			return user;
		} catch (DataAccessResourceFailureException e) {
			e.printStackTrace();
			return null;
		} catch (HibernateException e) {
			e.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	public void addEmployee(Object employee) {
		// TODO Auto-generated method stub
		this.getHibernateTemplate().save(employee);
	}

	@Override
	public String getHql(String name,String depart,String sex) {
		
		StringBuffer hql=new StringBuffer();
		hql.append("from Employee  employee,Employeeinfo  employeeinfo,Employeeskill employeeskill where employee.no=employeeinfo.employee.no and employee.no=employeeskill.employee.no and ");
		
		if(name==null||name.equals("")){
			if(depart==null||depart.equals("")){
				if(sex==null||sex.equals("")){
					hql.replace(hql.length()-5, hql.length()+1, "");
				}else{
					hql.append("employeeinfo.sex='"+sex+"'");
				}
			}else{
				hql.append("employeeinfo.department='"+depart+"' and ");
				if(sex==null||sex.equals("")){
					hql.replace(hql.length()-5, hql.length()+1, "");
				}else{
					hql.append("employeeinfo.sex='"+sex+"'");
				}
			}
		}else{
			hql.append("employee.name='"+name+"' and ");
			if(depart==null||depart.equals("")){
				if(sex==null||sex.equals("")){
					hql.replace(hql.length()-5, hql.length()+1, "");
				}else{
					hql.append("employeeinfo.sex='"+sex+"'");
				}
			}else{
				hql.append("employeeinfo.department='"+depart+"' and ");
				if(sex==null||sex.equals("")){
					hql.replace(hql.length()-5, hql.length()+1, "");
				}else{
					hql.append("employeeinfo.sex='"+sex+"'");
				}
			}
		}
		return hql.toString();
		
		
	}

	
	public List findWithPage(final Pager pager,final String hql) {
		
		return this.getHibernateTemplate().executeFind(new HibernateCallback(){    
		public Object doInHibernate(Session session) throws HibernateException ,SQLException{    
			//String hql="from Employee  employee,Employeeinfo  employeeinfo where employee.no=employeeinfo.employee.no";
			Query query=session.createQuery(hql);    
			query.setFirstResult(pager.getBeginIndex());    
		    query.setMaxResults(pager.getEveryPage());    
		    return query.list();    
		}    
		});    

//		try{
//			//String hql="from Employee  employee,Employeeinfo  employeeinfo where employee.no=employeeinfo.employee.no";
//			Query query=this.getSession().createQuery(hql);
//			query.setFirstResult(pager.getBeginIndex());
//			query.setMaxResults(pager.getEveryPage());
//			List list=query.list();
//			return list;
//		}catch(HibernateException he){
//			he.printStackTrace();
//			return null;
//		}
		
	}

	@Override
	public int getRows() {

		int totalRow=0;
		try{
			Query q=this.getSession().createQuery("select count(*) from Employee  employee,Employeeinfo  employeeinfo,Employeeskill employeeskill where employee.no=employeeinfo.employee.no and employee.no=employeeskill.employee.no");
			totalRow=((Long)q.uniqueResult()).intValue();
			
		}catch(HibernateException e){
			e.printStackTrace();	
		}
		return totalRow;
	}
	
	public void updateEmployee(Object employee){
		
		this.getHibernateTemplate().update(employee);
	}
//返回我想要的数据库的属性
	@Override
	public String returnString(String hql) {
		
try {
	Query query = this.getSession().createQuery(hql);
	String ID = query.uniqueResult().toString();
	return ID;
} catch (DataAccessResourceFailureException e) {
	e.printStackTrace();
	return null;
} catch (HibernateException e) {
	e.printStackTrace();
	return null;
} catch (Exception e) {
	e.printStackTrace();
	return null;
}
	}

	@Override
	public void deleteEmployee(Object employee) {

		this.getHibernateTemplate().delete(employee);
		
	}

	@Override
	public Employeeinfo getImage(int id) {

		String hql="from Employeeinfo where Id='"+id+"'";
		try {
			Query query = this.getSession().createQuery(hql);
			Employeeinfo info = (Employeeinfo) query.uniqueResult();
			return info;
		} catch (DataAccessResourceFailureException e) {
			e.printStackTrace();
			return null;
		} catch (HibernateException e) {
			e.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

}
